﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace UpdatePriceEbay
{
    class Database
    {
        private SqlConnection conn = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");
        private SqlConnection conn2 = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");
        
        public SqlDataReader reader;   // Va face legatura la fiecare produs

        // Pastrez un numar de rand curent

        public int descarcaProduse()
        {
            SqlCommand comanda = new SqlCommand("SELECT ISBN, UPC, ID FROM Product WHERE (linkEbay <> '') AND (ID NOT IN (SELECT DISTINCT product_id FROM Price WHERE (GETDATE() - 2 < timestamp) AND (site_id = 2)))", conn);
            comanda.CommandType = System.Data.CommandType.Text;            

            try
                {
                    conn.Open();
                    reader = comanda.ExecuteReader();
                }
                catch (Exception e)
                {
                    return -1;
                }
            
            return 0;
        }

        public int updatePrice(string id, double pretEbay, string linkEbay)
        {
                SqlCommand cmdInsertPrice = new SqlCommand("INSERT INTO Price(price,  product_id,  site_id, timestamp) "
                                                                   + " VALUES(@price, @product_id, 2      , getdate())", conn2);
                cmdInsertPrice.CommandType = System.Data.CommandType.Text;
                cmdInsertPrice.Parameters.Add("product_id", System.Data.SqlDbType.BigInt).Value = id;
                cmdInsertPrice.Parameters.Add("price", System.Data.SqlDbType.Float).Value = pretEbay;

                SqlCommand cmdUpdateProduct = new SqlCommand("UPDATE product SET linkEbay = @linkEbay ,  CurrPrice = case when CurrPrice < @price then CurrPrice else @price end WHERE id=@id", conn2);
                cmdUpdateProduct.CommandType = System.Data.CommandType.Text;
                cmdUpdateProduct.Parameters.Add("id", System.Data.SqlDbType.BigInt).Value = id;
                cmdUpdateProduct.Parameters.Add("linkEbay", System.Data.SqlDbType.VarChar).Value = linkEbay;
                cmdUpdateProduct.Parameters.Add("price", System.Data.SqlDbType.Float).Value = pretEbay;
    
                try
                {
                    conn2.Open();
                    cmdInsertPrice.ExecuteNonQuery();
                    cmdUpdateProduct.ExecuteNonQuery();
                }
                catch (Exception e)
                {                    
                    return -1;
                }
                finally
                {
                    conn2.Close();
                    cmdInsertPrice.Dispose();
                }

                cmdUpdateProduct.Dispose();

                return 0;
        }
                    
        public int inchideConxiune()
        {
            try
            {
                reader.Dispose();
                conn.Close();
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }
    }
}
